{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Complex queries\n",
    "## An example use of the Digital Twin\n",
    "\n",
    "In this notebook we are going to dive deep into queries:\n",
    "* Examining the customer experience through the lens of different aspects of the experience. \n",
    "\n",
    "In our previous steps we made:\n",
    "* Patrons, that have a `customer satisfaction`, a relationship with tickets, and locations.\n",
    "* Tickets that are `owned by customers`\n",
    "* Lines that lead to areas\n",
    "* Areas where people are located. \n",
    "\n",
    "We will be doing a bunch of different queries on this theme. \n",
    "\n",
    "\n",
    "[This is the SDK repo on Github](https://github.com/Azure/azure-sdk-for-python/tree/4559e19e2f3146a49f1eba1706bb798071f4a1f5/sdk/digitaltwins/azure-digitaltwins-core)\n",
    "\n",
    "[Here is the doc on the query language](https://docs.microsoft.com/en-us/azure/digital-twins/concepts-query-language)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<azure.digitaltwins.core._digitaltwins_client.DigitalTwinsClient at 0x27d4a46b3c8>"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from azure.identity import AzureCliCredential\n",
    "from azure.digitaltwins.core import DigitalTwinsClient\n",
    "\n",
    "# using yaml instead of \n",
    "import yaml\n",
    "import uuid\n",
    "\n",
    "# using altair instead of matplotlib for vizuals\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "# you will get this from the ADT resource at portal.azure.com\n",
    "your_digital_twin_url = \"home-test-twin.api.wcus.digitaltwins.azure.net\"\n",
    "\n",
    "azure_cli = AzureCliCredential()\n",
    "service_client = DigitalTwinsClient(\n",
    "    your_digital_twin_url, azure_cli)\n",
    "service_client"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I'm going to set up a generic function that runs queries and gets the data. This will keep me from doing it over and over. \n",
    "\n",
    "**Note that with really large models this might perform poorly** I'm only doing this here as this example is very small. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>$dtId</th>\n",
       "      <th>$etag</th>\n",
       "      <th>satisfaction</th>\n",
       "      <th>totalWaitTime</th>\n",
       "      <th>$metadata</th>\n",
       "      <th>event_title</th>\n",
       "      <th>state</th>\n",
       "      <th>ticket_location</th>\n",
       "      <th>capacity</th>\n",
       "      <th>status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>customer-cc04f3b6-39b0-4cef-bfff-a7d668cce446</td>\n",
       "      <td>W/\"158b01df-d555-4c1b-bf44-9c1e8c63eb02\"</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>{'$model': 'dtmi:billmanh:patron;1', 'satisfac...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>customer-3cbd5e60-957d-44ff-944f-9adb42a20a52</td>\n",
       "      <td>W/\"5d7359d5-e0f6-44e7-80f8-2a3f191295f2\"</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>{'$model': 'dtmi:billmanh:patron;1', 'satisfac...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>customer-5c454e2f-f70b-4352-b75a-958f1a49beba</td>\n",
       "      <td>W/\"d7a8af67-7b40-4ed8-94c4-553fb639e197\"</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>{'$model': 'dtmi:billmanh:patron;1', 'satisfac...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>customer-26196fee-5ffd-457a-86b7-192a998f3cf2</td>\n",
       "      <td>W/\"ee1d4d68-aa14-4d35-a029-7635675f24d7\"</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>{'$model': 'dtmi:billmanh:patron;1', 'satisfac...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>customer-e6f49d8a-711b-41c3-9db8-c7ece3dbc32c</td>\n",
       "      <td>W/\"d1242800-1c21-4bab-909f-8dc49ef0ce92\"</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>{'$model': 'dtmi:billmanh:patron;1', 'satisfac...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117</th>\n",
       "      <td>ticket-dc48bc69-b1f5-4850-b5af-037c4ea72961</td>\n",
       "      <td>W/\"5ea29c9e-a6be-4c02-979a-df1b2aa8cb5c\"</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'$model': 'dtmi:billmanh:ticket;1', 'event_ti...</td>\n",
       "      <td>Smashing Pumpkins</td>\n",
       "      <td>open</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>ticket-92826a40-b024-4b91-a4d2-7e20dcfddce3</td>\n",
       "      <td>W/\"002990f3-4ed8-464c-a90e-4b83ae5362fb\"</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'$model': 'dtmi:billmanh:ticket;1', 'event_ti...</td>\n",
       "      <td>Foo Fighters</td>\n",
       "      <td>open</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>ticket-8c483b5c-37d4-4027-8d67-d920bf8ca063</td>\n",
       "      <td>W/\"22d419c0-a6da-4935-82e3-5b1f7236908e\"</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'$model': 'dtmi:billmanh:ticket;1', 'event_ti...</td>\n",
       "      <td>Foo Fighters</td>\n",
       "      <td>open</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>ticket-09028447-8e23-4e4f-a100-098a4dc8e919</td>\n",
       "      <td>W/\"39cc602c-a41e-47bf-8da9-1a401419832a\"</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'$model': 'dtmi:billmanh:ticket;1', 'event_ti...</td>\n",
       "      <td>Foo Fighters</td>\n",
       "      <td>open</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>ticket-ccc89397-0d52-42a3-8fe1-39c97158c87c</td>\n",
       "      <td>W/\"094b5fa9-4443-4923-b8f9-24201fb2f26f\"</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'$model': 'dtmi:billmanh:ticket;1', 'event_ti...</td>\n",
       "      <td>Foo Fighters</td>\n",
       "      <td>open</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>122 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             $dtId  \\\n",
       "0    customer-cc04f3b6-39b0-4cef-bfff-a7d668cce446   \n",
       "1    customer-3cbd5e60-957d-44ff-944f-9adb42a20a52   \n",
       "2    customer-5c454e2f-f70b-4352-b75a-958f1a49beba   \n",
       "3    customer-26196fee-5ffd-457a-86b7-192a998f3cf2   \n",
       "4    customer-e6f49d8a-711b-41c3-9db8-c7ece3dbc32c   \n",
       "..                                             ...   \n",
       "117    ticket-dc48bc69-b1f5-4850-b5af-037c4ea72961   \n",
       "118    ticket-92826a40-b024-4b91-a4d2-7e20dcfddce3   \n",
       "119    ticket-8c483b5c-37d4-4027-8d67-d920bf8ca063   \n",
       "120    ticket-09028447-8e23-4e4f-a100-098a4dc8e919   \n",
       "121    ticket-ccc89397-0d52-42a3-8fe1-39c97158c87c   \n",
       "\n",
       "                                        $etag  satisfaction  totalWaitTime  \\\n",
       "0    W/\"158b01df-d555-4c1b-bf44-9c1e8c63eb02\"          10.0           10.0   \n",
       "1    W/\"5d7359d5-e0f6-44e7-80f8-2a3f191295f2\"          10.0           10.0   \n",
       "2    W/\"d7a8af67-7b40-4ed8-94c4-553fb639e197\"           7.0            0.0   \n",
       "3    W/\"ee1d4d68-aa14-4d35-a029-7635675f24d7\"           9.0            0.0   \n",
       "4    W/\"d1242800-1c21-4bab-909f-8dc49ef0ce92\"           7.0            0.0   \n",
       "..                                        ...           ...            ...   \n",
       "117  W/\"5ea29c9e-a6be-4c02-979a-df1b2aa8cb5c\"           NaN            NaN   \n",
       "118  W/\"002990f3-4ed8-464c-a90e-4b83ae5362fb\"           NaN            NaN   \n",
       "119  W/\"22d419c0-a6da-4935-82e3-5b1f7236908e\"           NaN            NaN   \n",
       "120  W/\"39cc602c-a41e-47bf-8da9-1a401419832a\"           NaN            NaN   \n",
       "121  W/\"094b5fa9-4443-4923-b8f9-24201fb2f26f\"           NaN            NaN   \n",
       "\n",
       "                                             $metadata        event_title  \\\n",
       "0    {'$model': 'dtmi:billmanh:patron;1', 'satisfac...                NaN   \n",
       "1    {'$model': 'dtmi:billmanh:patron;1', 'satisfac...                NaN   \n",
       "2    {'$model': 'dtmi:billmanh:patron;1', 'satisfac...                NaN   \n",
       "3    {'$model': 'dtmi:billmanh:patron;1', 'satisfac...                NaN   \n",
       "4    {'$model': 'dtmi:billmanh:patron;1', 'satisfac...                NaN   \n",
       "..                                                 ...                ...   \n",
       "117  {'$model': 'dtmi:billmanh:ticket;1', 'event_ti...  Smashing Pumpkins   \n",
       "118  {'$model': 'dtmi:billmanh:ticket;1', 'event_ti...       Foo Fighters   \n",
       "119  {'$model': 'dtmi:billmanh:ticket;1', 'event_ti...       Foo Fighters   \n",
       "120  {'$model': 'dtmi:billmanh:ticket;1', 'event_ti...       Foo Fighters   \n",
       "121  {'$model': 'dtmi:billmanh:ticket;1', 'event_ti...       Foo Fighters   \n",
       "\n",
       "    state ticket_location  capacity status  \n",
       "0     NaN             NaN       NaN    NaN  \n",
       "1     NaN             NaN       NaN    NaN  \n",
       "2     NaN             NaN       NaN    NaN  \n",
       "3     NaN             NaN       NaN    NaN  \n",
       "4     NaN             NaN       NaN    NaN  \n",
       "..    ...             ...       ...    ...  \n",
       "117  open               4       NaN    NaN  \n",
       "118  open               1       NaN    NaN  \n",
       "119  open               2       NaN    NaN  \n",
       "120  open               3       NaN    NaN  \n",
       "121  open               4       NaN    NaN  \n",
       "\n",
       "[122 rows x 10 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def query_ADT(query):\n",
    "    query_result = service_client.query_twins(query)\n",
    "    values = [i for i in query_result]\n",
    "    return values\n",
    "\n",
    "def query_to_df(query):\n",
    "    query_result = query_ADT(query)\n",
    "    values = pd.DataFrame(query_result)\n",
    "    return values\n",
    "\n",
    "query_expression = \"SELECT * FROM digitaltwins\"\n",
    "query_to_df(query_expression)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that the larger query will give you back all of the values, so you can pop it into a dataframe and filter on the `$metadata` to get the values you want"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## IS_OF_MODEL\n",
    "The process for most analysis is to query the items that are relevant into a dataframe and do your analysis on them. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    84.000000\n",
       "mean      7.142857\n",
       "std       1.529965\n",
       "min       5.000000\n",
       "25%       6.000000\n",
       "50%       7.000000\n",
       "75%       8.250000\n",
       "max      10.000000\n",
       "Name: satisfaction, dtype: float64"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_expression = \"SELECT * FROM digitaltwins where IS_OF_MODEL('dtmi:mymodels:patron;1')\"\n",
    "customers = query_to_df(query_expression)\n",
    "customers.satisfaction.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>T</th>\n",
       "      <th>CT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>{'$dtId': 'customer-cc04f3b6-39b0-4cef-bfff-a7...</td>\n",
       "      <td>{'$dtId': 'line-2', '$etag': 'W/\"dd64cd24-6ec2...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>{'$dtId': 'customer-25e19268-3433-4f09-afe3-94...</td>\n",
       "      <td>{'$dtId': 'line-2', '$etag': 'W/\"dd64cd24-6ec2...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>{'$dtId': 'customer-c87adbfa-1c6e-4ea9-9f03-83...</td>\n",
       "      <td>{'$dtId': 'line-2', '$etag': 'W/\"dd64cd24-6ec2...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   T  \\\n",
       "0  {'$dtId': 'customer-cc04f3b6-39b0-4cef-bfff-a7...   \n",
       "1  {'$dtId': 'customer-25e19268-3433-4f09-afe3-94...   \n",
       "2  {'$dtId': 'customer-c87adbfa-1c6e-4ea9-9f03-83...   \n",
       "\n",
       "                                                  CT  \n",
       "0  {'$dtId': 'line-2', '$etag': 'W/\"dd64cd24-6ec2...  \n",
       "1  {'$dtId': 'line-2', '$etag': 'W/\"dd64cd24-6ec2...  \n",
       "2  {'$dtId': 'line-2', '$etag': 'W/\"dd64cd24-6ec2...  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_expression =\"\"\"\n",
    "SELECT T, CT\n",
    "FROM DIGITALTWINS T\n",
    "JOIN CT RELATED T.locatedIn\n",
    "WHERE CT.$dtId = 'line-2'\n",
    "\"\"\"\n",
    "\n",
    "customers_in_area_2 = query_to_df(query_expression)\n",
    "customers_in_area_2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ok let's unpack that:\n",
    "\n",
    "`SELECT T, CT` - means to give short names to all the different classes, so in this case `T` refers to all twins, and CT refers to a related item. The related item comes out in the second column\n",
    "\n",
    "`RELATED T.locatedIn` - gits all of elements that have a `locatedIn` relationship and stores it in `CT`. \n",
    "\n",
    "`WHERE CT.$dtId = 'line-2'` - limits the query to items that have that relationship with `line-2`. This is the filter part. \n",
    "\n",
    "**Note** it seems that all _joined queries_ require a specific twin by name. \n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "T     {'$dtId': 'customer-cc04f3b6-39b0-4cef-bfff-a7...\n",
       "CT    {'$dtId': 'line-2', '$etag': 'W/\"dd64cd24-6ec2...\n",
       "Name: 0, dtype: object"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers_in_area_2.loc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'$dtId': 'line-2',\n",
       " '$etag': 'W/\"dd64cd24-6ec2-452d-b17a-83ebe4bcd73a\"',\n",
       " 'capacity': 3,\n",
       " '$metadata': {'$model': 'dtmi:billmanh:line;1',\n",
       "  'capacity': {'lastUpdateTime': '2020-11-30T00:10:31.5945931Z'}}}"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers_in_area_2.loc[0,'CT']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So let's look at the customers in `line-2`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'$dtId': 'customer-cc04f3b6-39b0-4cef-bfff-a7d668cce446',\n",
       " '$etag': 'W/\"158b01df-d555-4c1b-bf44-9c1e8c63eb02\"',\n",
       " 'satisfaction': 10,\n",
       " 'totalWaitTime': 10,\n",
       " '$metadata': {'$model': 'dtmi:billmanh:patron;1',\n",
       "  'satisfaction': {'lastUpdateTime': '2020-11-19T02:17:30.1401999Z'},\n",
       "  'totalWaitTime': {'lastUpdateTime': '2020-11-19T02:17:30.1401999Z'}}}"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers_in_area_2.loc[0,'T']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>$dtId</th>\n",
       "      <th>$etag</th>\n",
       "      <th>satisfaction</th>\n",
       "      <th>totalWaitTime</th>\n",
       "      <th>$metadata</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>customer-cc04f3b6-39b0-4cef-bfff-a7d668cce446</td>\n",
       "      <td>W/\"158b01df-d555-4c1b-bf44-9c1e8c63eb02\"</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>{'$model': 'dtmi:billmanh:patron;1', 'satisfac...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>customer-25e19268-3433-4f09-afe3-94f466313368</td>\n",
       "      <td>W/\"91509c0d-7dc9-465b-a735-2fde2f67fe02\"</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>{'$model': 'dtmi:billmanh:patron;1', 'satisfac...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>customer-c87adbfa-1c6e-4ea9-9f03-83e3877ef5fc</td>\n",
       "      <td>W/\"44f9fd9b-141f-4603-8a0b-ad8a3730ed0d\"</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>{'$model': 'dtmi:billmanh:patron;1', 'satisfac...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           $dtId  \\\n",
       "0  customer-cc04f3b6-39b0-4cef-bfff-a7d668cce446   \n",
       "1  customer-25e19268-3433-4f09-afe3-94f466313368   \n",
       "2  customer-c87adbfa-1c6e-4ea9-9f03-83e3877ef5fc   \n",
       "\n",
       "                                      $etag  satisfaction  totalWaitTime  \\\n",
       "0  W/\"158b01df-d555-4c1b-bf44-9c1e8c63eb02\"            10             10   \n",
       "1  W/\"91509c0d-7dc9-465b-a735-2fde2f67fe02\"            10             10   \n",
       "2  W/\"44f9fd9b-141f-4603-8a0b-ad8a3730ed0d\"             8              0   \n",
       "\n",
       "                                           $metadata  \n",
       "0  {'$model': 'dtmi:billmanh:patron;1', 'satisfac...  \n",
       "1  {'$model': 'dtmi:billmanh:patron;1', 'satisfac...  \n",
       "2  {'$model': 'dtmi:billmanh:patron;1', 'satisfac...  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "l2_cust = pd.DataFrame(customers_in_area_2['T'].tolist())\n",
    "l2_cust"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count     3.000000\n",
       "mean      9.333333\n",
       "std       1.154701\n",
       "min       8.000000\n",
       "25%       9.000000\n",
       "50%      10.000000\n",
       "75%      10.000000\n",
       "max      10.000000\n",
       "Name: satisfaction, dtype: float64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "l2_cust.satisfaction.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    84.000000\n",
       "mean      7.142857\n",
       "std       1.529965\n",
       "min       5.000000\n",
       "25%       6.000000\n",
       "50%       7.000000\n",
       "75%       8.250000\n",
       "max      10.000000\n",
       "Name: satisfaction, dtype: float64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers.satisfaction.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Customers in line 2 have higher satisfaction than customers in general. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## How many people in each line"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>COUNT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   COUNT\n",
       "0      5"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT COUNT()  \n",
    "FROM DIGITALTWINS T  \n",
    "JOIN CT RELATED T.locatedIn  \n",
    "WHERE CT.$dtId IN ['line-0','line-1','line-2', 'line-3']\n",
    "\"\"\"\n",
    "\n",
    "customers_in_lines = query_to_df(query)\n",
    "customers_in_lines"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>COUNT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   COUNT\n",
       "0      3"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT COUNT()  \n",
    "FROM DIGITALTWINS T  \n",
    "JOIN CT RELATED T.locatedIn  \n",
    "WHERE CT.$dtId IN ['line-2']\n",
    "\"\"\"\n",
    "\n",
    "customers_in_lines = query_to_df(query)\n",
    "customers_in_lines"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The rough part is that you can only get one count back, not a count per line, like you could with propper SQL. You also have to hard code all of your `$dtID` as they require literal values. Lame.\n",
    "\n",
    "Here is the way around that. \n",
    "\n",
    "`SELECT line, customer` <- select the columns that you want the query to return\n",
    "\n",
    "`AND IS_OF_MODEL(customer, 'dtmi:billmanh:patron;1')` <- specify that `customer` are twins of the `patron` model. \n",
    "\n",
    "You still have to hard type the names of the lines, or rooms or whatever, but it returns all of the customers in all of the lines. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>line</th>\n",
       "      <th>customer</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>{'$dtId': 'line-1', '$etag': 'W/\"aa93fee2-cba0...</td>\n",
       "      <td>{'$dtId': 'customer-e6f49d8a-711b-41c3-9db8-c7...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>{'$dtId': 'line-1', '$etag': 'W/\"aa93fee2-cba0...</td>\n",
       "      <td>{'$dtId': 'customer-21e17d28-76c3-4c04-8df9-39...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>{'$dtId': 'line-2', '$etag': 'W/\"dd64cd24-6ec2...</td>\n",
       "      <td>{'$dtId': 'customer-cc04f3b6-39b0-4cef-bfff-a7...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>{'$dtId': 'line-2', '$etag': 'W/\"dd64cd24-6ec2...</td>\n",
       "      <td>{'$dtId': 'customer-25e19268-3433-4f09-afe3-94...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>{'$dtId': 'line-2', '$etag': 'W/\"dd64cd24-6ec2...</td>\n",
       "      <td>{'$dtId': 'customer-c87adbfa-1c6e-4ea9-9f03-83...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                line  \\\n",
       "0  {'$dtId': 'line-1', '$etag': 'W/\"aa93fee2-cba0...   \n",
       "1  {'$dtId': 'line-1', '$etag': 'W/\"aa93fee2-cba0...   \n",
       "2  {'$dtId': 'line-2', '$etag': 'W/\"dd64cd24-6ec2...   \n",
       "3  {'$dtId': 'line-2', '$etag': 'W/\"dd64cd24-6ec2...   \n",
       "4  {'$dtId': 'line-2', '$etag': 'W/\"dd64cd24-6ec2...   \n",
       "\n",
       "                                            customer  \n",
       "0  {'$dtId': 'customer-e6f49d8a-711b-41c3-9db8-c7...  \n",
       "1  {'$dtId': 'customer-21e17d28-76c3-4c04-8df9-39...  \n",
       "2  {'$dtId': 'customer-cc04f3b6-39b0-4cef-bfff-a7...  \n",
       "3  {'$dtId': 'customer-25e19268-3433-4f09-afe3-94...  \n",
       "4  {'$dtId': 'customer-c87adbfa-1c6e-4ea9-9f03-83...  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT line, customer\n",
    "FROM DIGITALTWINS customer\n",
    "JOIN line RELATED customer.locatedIn\n",
    "WHERE line.$dtId IN ['line-0','line-1','line-2', 'line-3']\n",
    "AND IS_OF_MODEL(customer, 'dtmi:mymodels:patron;1')\n",
    "\"\"\"\n",
    "\n",
    "customers_in_lines = query_to_df(query)\n",
    "customers_in_lines"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Easy enough to munge it into a dataframe:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>line</th>\n",
       "      <th>$etag</th>\n",
       "      <th>capacity</th>\n",
       "      <th>$metadata</th>\n",
       "      <th>customer</th>\n",
       "      <th>$etag</th>\n",
       "      <th>satisfaction</th>\n",
       "      <th>totalWaitTime</th>\n",
       "      <th>$metadata</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>line-1</td>\n",
       "      <td>W/\"aa93fee2-cba0-4bd4-a020-114f058b0f01\"</td>\n",
       "      <td>3</td>\n",
       "      <td>{'$model': 'dtmi:billmanh:line;1', 'capacity':...</td>\n",
       "      <td>customer-e6f49d8a-711b-41c3-9db8-c7ece3dbc32c</td>\n",
       "      <td>W/\"d1242800-1c21-4bab-909f-8dc49ef0ce92\"</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>{'$model': 'dtmi:billmanh:patron;1', 'satisfac...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>line-1</td>\n",
       "      <td>W/\"aa93fee2-cba0-4bd4-a020-114f058b0f01\"</td>\n",
       "      <td>3</td>\n",
       "      <td>{'$model': 'dtmi:billmanh:line;1', 'capacity':...</td>\n",
       "      <td>customer-21e17d28-76c3-4c04-8df9-396703692a68</td>\n",
       "      <td>W/\"c62357a9-f4e5-4ec6-9d60-08b0da1125a7\"</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>{'$model': 'dtmi:billmanh:patron;1', 'satisfac...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>line-2</td>\n",
       "      <td>W/\"dd64cd24-6ec2-452d-b17a-83ebe4bcd73a\"</td>\n",
       "      <td>3</td>\n",
       "      <td>{'$model': 'dtmi:billmanh:line;1', 'capacity':...</td>\n",
       "      <td>customer-cc04f3b6-39b0-4cef-bfff-a7d668cce446</td>\n",
       "      <td>W/\"158b01df-d555-4c1b-bf44-9c1e8c63eb02\"</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>{'$model': 'dtmi:billmanh:patron;1', 'satisfac...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>line-2</td>\n",
       "      <td>W/\"dd64cd24-6ec2-452d-b17a-83ebe4bcd73a\"</td>\n",
       "      <td>3</td>\n",
       "      <td>{'$model': 'dtmi:billmanh:line;1', 'capacity':...</td>\n",
       "      <td>customer-25e19268-3433-4f09-afe3-94f466313368</td>\n",
       "      <td>W/\"91509c0d-7dc9-465b-a735-2fde2f67fe02\"</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>{'$model': 'dtmi:billmanh:patron;1', 'satisfac...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>line-2</td>\n",
       "      <td>W/\"dd64cd24-6ec2-452d-b17a-83ebe4bcd73a\"</td>\n",
       "      <td>3</td>\n",
       "      <td>{'$model': 'dtmi:billmanh:line;1', 'capacity':...</td>\n",
       "      <td>customer-c87adbfa-1c6e-4ea9-9f03-83e3877ef5fc</td>\n",
       "      <td>W/\"44f9fd9b-141f-4603-8a0b-ad8a3730ed0d\"</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>{'$model': 'dtmi:billmanh:patron;1', 'satisfac...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     line                                     $etag  capacity  \\\n",
       "0  line-1  W/\"aa93fee2-cba0-4bd4-a020-114f058b0f01\"         3   \n",
       "1  line-1  W/\"aa93fee2-cba0-4bd4-a020-114f058b0f01\"         3   \n",
       "2  line-2  W/\"dd64cd24-6ec2-452d-b17a-83ebe4bcd73a\"         3   \n",
       "3  line-2  W/\"dd64cd24-6ec2-452d-b17a-83ebe4bcd73a\"         3   \n",
       "4  line-2  W/\"dd64cd24-6ec2-452d-b17a-83ebe4bcd73a\"         3   \n",
       "\n",
       "                                           $metadata  \\\n",
       "0  {'$model': 'dtmi:billmanh:line;1', 'capacity':...   \n",
       "1  {'$model': 'dtmi:billmanh:line;1', 'capacity':...   \n",
       "2  {'$model': 'dtmi:billmanh:line;1', 'capacity':...   \n",
       "3  {'$model': 'dtmi:billmanh:line;1', 'capacity':...   \n",
       "4  {'$model': 'dtmi:billmanh:line;1', 'capacity':...   \n",
       "\n",
       "                                        customer  \\\n",
       "0  customer-e6f49d8a-711b-41c3-9db8-c7ece3dbc32c   \n",
       "1  customer-21e17d28-76c3-4c04-8df9-396703692a68   \n",
       "2  customer-cc04f3b6-39b0-4cef-bfff-a7d668cce446   \n",
       "3  customer-25e19268-3433-4f09-afe3-94f466313368   \n",
       "4  customer-c87adbfa-1c6e-4ea9-9f03-83e3877ef5fc   \n",
       "\n",
       "                                      $etag  satisfaction  totalWaitTime  \\\n",
       "0  W/\"d1242800-1c21-4bab-909f-8dc49ef0ce92\"             7              0   \n",
       "1  W/\"c62357a9-f4e5-4ec6-9d60-08b0da1125a7\"             8              0   \n",
       "2  W/\"158b01df-d555-4c1b-bf44-9c1e8c63eb02\"            10             10   \n",
       "3  W/\"91509c0d-7dc9-465b-a735-2fde2f67fe02\"            10             10   \n",
       "4  W/\"44f9fd9b-141f-4603-8a0b-ad8a3730ed0d\"             8              0   \n",
       "\n",
       "                                           $metadata  \n",
       "0  {'$model': 'dtmi:billmanh:patron;1', 'satisfac...  \n",
       "1  {'$model': 'dtmi:billmanh:patron;1', 'satisfac...  \n",
       "2  {'$model': 'dtmi:billmanh:patron;1', 'satisfac...  \n",
       "3  {'$model': 'dtmi:billmanh:patron;1', 'satisfac...  \n",
       "4  {'$model': 'dtmi:billmanh:patron;1', 'satisfac...  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c_in_line = pd.concat(\n",
    "    [pd.DataFrame(customers_in_lines['line'].tolist()),\n",
    "    pd.DataFrame(customers_in_lines['customer'].tolist())],\n",
    "    axis=1\n",
    ")\n",
    "\n",
    "cols = c_in_line.columns.tolist()\n",
    "cols[0] = 'line'\n",
    "cols[4] = 'customer'\n",
    "c_in_line.columns = cols\n",
    "c_in_line"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many people are in each line:  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "line\n",
       "line-1    2\n",
       "line-2    3\n",
       "Name: customer, dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c_in_line.groupby('line').count()['customer']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Which group of people has the highest satisfaction?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "line\n",
       "line-1    7.500000\n",
       "line-2    9.333333\n",
       "Name: satisfaction, dtype: float64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c_in_line.groupby('line').mean()['satisfaction']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python (azure_test)",
   "language": "python",
   "name": "azure_test"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}